package com.gdzl_hadoop.util;

import com.gdzl_hadoop.common.Const;
import com.healthmarketscience.jackcess.Column;
import com.healthmarketscience.jackcess.DatabaseBuilder;
import com.healthmarketscience.jackcess.Row;
import com.healthmarketscience.jackcess.Table;
import org.apache.commons.lang3.StringUtils;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class AccessUtil {

    private static final String YXTCHD = "YXTCHD";//有效土层厚度(0,30],(30-60],(60-100],(100,)
    private static final String BCTRZD = "BCTRZD";//表层土壤质地
    private static final String PMGX = "PMGX";//剖面构型
    private static final String TRYJZHL = "TRYJZHL";//土壤有机质含量(0,6],(6,10],(10,20],(20,]
    private static final String TRSJD = "TRSJD";//土壤酸碱度<0,4.5],(4.5,  5],(5.0,5.5],(5.5,6.0],(6.0-7.9],(7.9,8.5],(8.5,9], (9,]
    private static final String PSTJ = "PSTJ";//排水条件
    private static final String DXPD = "DXPD";//地形坡度
    private static final String GGBZL = "GGBZL";//灌溉保证率
    private static final String TMPD = "TMPD";//田面坡度
    private static final String DXSW = "DXSW";//地下水位(0,30]、(30,60]、(60,)
    private static final List<String> facts = new ArrayList<>();
    private static final List<String> cutData = new ArrayList<>();

    static {
        facts.add(YXTCHD);
        facts.add(BCTRZD);
        facts.add(PMGX);
        facts.add(TRYJZHL);
        facts.add(TRSJD);
        facts.add(PSTJ);
        facts.add(DXPD);
        facts.add(GGBZL);
        facts.add(TMPD);
        facts.add(DXSW);
        cutData.add(YXTCHD);
        cutData.add(DXSW);
        cutData.add(TRYJZHL);
        cutData.add(TRSJD);
    }

    public static StringBuffer InitialFpgData(File file, String tableName) {
        Table table = null;
        StringBuffer sb = new StringBuffer();
        try {
            table = DatabaseBuilder.open(file).getTable(tableName);

            for (Row row : table) {
                for (Column column : table.getColumns()) {
                    String columnName = column.getName();
                    if (!facts.contains(columnName))
                        continue;
                    Object value = row.get(columnName);
                    if (!cutData.contains(columnName)) {
                        if (!(value instanceof String)) {
                            value = String.valueOf(value).trim();
                        }
                        sb.append(columnName + ":" + value + ",");
                    } else {

                        switch (columnName) {
                            case DXSW:
                                if ((short) value >= 0 && (short) value <= 30) {
                                    value = 30;
                                    value = String.valueOf(value).trim();
                                    sb.append(columnName + ":" + value + ",");
                                } else if ((short) value > 30 && (short) value <= 60) {
                                    value = 60;
                                    value = String.valueOf(value).trim();
                                    sb.append(columnName + ":" + value + ",");
                                } else if ((short) value > 60) {
                                    sb.append(columnName + ":" + "more,");
                                }
                                break;
                            case TRSJD:
                                if ((float) value >= 0 && (float) value <= 4.5) {
                                    value = (float) 4.5;
                                } else if ((float) value > 4.5 && (float) value <= 5) {
                                    value = (float) 5.0;
                                } else if ((float) value > 5 && (float) value <= 5.5) {
                                    value = (float) 5.5;
                                } else if ((float) value > 5.5 && (float) value <= 6.0) {
                                    value = (float) 6.0;
                                } else if ((float) value > 6.0 && (float) value <= 7.9) {
                                    value = (float) 7.9;
                                } else if ((float) value > 7.9 && (float) value <= 8.5) {
                                    value = (float) 8.5;
                                } else if ((float) value > 8.5 && (float) value <= 9) {
                                    value = (float) 9.0;
                                }
                                value = String.valueOf(value).trim();
                                sb.append(columnName + ":" + value + ",");
                                break;
                            case TRYJZHL:
                                if ((float) value >= 0 && (float) value <= 6) {
                                    value = (float) 6;
                                    value = String.valueOf(value).trim();
                                    sb.append(columnName + ":" + value + ",");
                                } else if ((float) value > 6 && (float) value <= 10) {
                                    value = (float) 10.0;
                                    value = String.valueOf(value).trim();
                                    sb.append(columnName + ":" + value + ",");
                                } else if ((float) value > 10 && (float) value <= 20) {
                                    value = (float) 20.0;
                                    value = String.valueOf(value).trim();
                                    sb.append(columnName + ":" + value + ",");
                                } else if ((Float) value > 20) {
                                    sb.append(columnName + ":" + "more,");
                                }
                                break;
                            case YXTCHD:
                                if ((Short) value >= 0 && (Short) value <= 30) {
                                    value = 30;
                                } else if ((Short) value > 30 && (Short) value <= 60) {
                                    value = 60;
                                } else if ((Short) value > 60 && (Short) value <= 100) {
                                    value = 100;
                                }
                                value = String.valueOf(value).trim();
                                sb.append(columnName + ":" + value + ",");
                                break;
                        }

                    }

                }//end columns
                sb.deleteCharAt(sb.length() - 1);
                sb.append("\n");
            }
            sb.deleteCharAt(sb.length() - 1);
            return sb;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return sb;
    }


    //   Row row=table.getNextRow();
          /*  for(Column column : table.getColumns()) {
                String columnName = column.getName();
                Object value = row.get(columnName);
               System.out.println("Column " + columnName + "(" + column.getType() + "): "
                       + value + " (" + value.getClass() + ")");

            }*/
            /*InputStream in=new ByteArrayInputStream((byte[])row.get("Shape"));
            System.out.println(in);
            byte[] tempbytes = new byte[100];
            int byteread = 0;*/
   /* public static void ucanAccess() throws SQLException, IOException {
        try {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");// 这个驱动的地址不要改
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        // Connection
        // con=DriverManager.getConnection("jdbc:ucanaccess://D:\\Database3.mdb","123456","123456");
        Connection con = DriverManager.getConnection("jdbc:ucanaccess://D:\\Documents\\Desktop\\2015XJFDDY.mdb");
        Statement s = con.createStatement();
        s.execute("select Shape from XJFDDY where objectid=1 limit 1");
        ResultSet rs = s.getResultSet();
        rs.next();

        InputStream isDB = rs.getBinaryStream("Shape");
        System.out.println(isDB);
        File fl = new File("CopyElisaArt.JPG");
        OutputStream outFile = new FileOutputStream(fl);
        byte[] ba = new byte[4096];
        int len;
        while ((len = isDB.read(ba)) != -1) {
            outFile.write(ba, 0, len); }
        outFile.flush();
        outFile.close();
        System.out.println("CopyElisaArt.JPG was created in " + fl.getAbsolutePath());

    }*/

}
